/**
 *category 高级检索条件下的列表显示方法
 *author 老贺整理
 *date 2015-06-28
 * 
 */
package teacher.service.laohe;

import java.text.SimpleDateFormat;
import java.util.*;

import teacher.bean.Award;
import teacher.bean.Paper;
import teacher.bean.Patent;
import teacher.bean.Project;
import teacher.bean.Teacher;
import teacher.bean.Treatise;
import tools.StringDate;
import database.dao.MysqlCrud;

public class ListShow {
	
//获奖显示列表开始
	public List<Award> getAwardByUser(Award award,String date2,Teacher teacher){
//		//String sql= "SELECT * FROM award WHERE state=? and awaId LIMIT " +
//				""+(pageNow-1)+" ,"+pageSize+"";
		int j=0;
		List<Award> awards=new ArrayList<Award>();
		String sql="select * from (select * from award order by state,kind,level,date desc) as acheve , teacher as T where acheve.teaId=T.teaId and T.user=? ";
		List<String> sql1=new ArrayList<String>();
		List<String> paras=new ArrayList<String>();
		
		paras.add(teacher.getUser());
		System.out.println("参数的尺寸为:"+paras.size());
		if(award!=null)
		{
			if(award.getName()!=null)
			{
				sql1.add(" acheve.name like ? ");
				paras.add("%"+String.valueOf(award.getName())+"%");
			}
			if(award.getProjectName()!=null)
			{
				sql1.add(" acheve.projectName like ? ");
				paras.add("%"+String.valueOf(award.getProjectName())+"%");
			}
			if(award.getLevel()!=null)
			{
				sql1.add(" acheve.level=? ");
				paras.add(String.valueOf(award.getLevel()));
			}
			if(award.getPeople()!=null)
			{
				sql1.add(" acheve.people like ? ");
				paras.add("%"+String.valueOf(award.getPeople())+"%");
			}
			if(award.getDate()!=null)
			{
				sql1.add(" acheve.date>=? and A.date<=? ");
				paras.add(String.valueOf(award.getDate()));
				paras.add(date2);
				
			}
			if(award.getKind()!=null)
			{
				sql1.add(" acheve.kind=? ");
				paras.add(String.valueOf(award.getKind()));
			}
			if(award.getEmpowerUnit()!=null)
			{
				sql1.add(" acheve.empowerUnit=? ");
				paras.add(String.valueOf(award.getEmpowerUnit()));
			}
		}
		for(int i=0;i<sql1.size();i++)
		{
			sql+="and"+sql1.get(i);
		}
		String[] parameter=new String[paras.size()];
		for(int i=0;i<paras.size();i++)
		{
			parameter[i]=paras.get(i);
		}
		MysqlCrud mysqlcurd=new MysqlCrud();
		List<Map> list = mysqlcurd.SelectData(sql, parameter);
		
	while(j<list.size()){
		Map map=list.get(j);
		try {
			Award a = new Award();
			a.setAwaId(Integer.parseInt(map.get(0).toString()));
			a.setName(map.get(1).toString());
			a.setProjectName(map.get(2).toString());
			a.setLevel(Integer.parseInt(map.get(3).toString()));
			a.setPeople(map.get(4).toString());
			a.setDate(new SimpleDateFormat("yyyy-MM-dd").parse(map.get(5).toString()));
			a.setNumber(map.get(6).toString());
			a.setUpload(map.get(7).toString());
			a.setTeaId(Integer.parseInt(map.get(8).toString()));
			a.setState(Integer.parseInt(map.get(9).toString()));			
			a.setKind(Integer.parseInt(map.get(10).toString()));
			a.setUnitNum(Integer.parseInt(map.get(11).toString()));
			a.setEmpowerUnit(map.get(12).toString());
			awards.add(a);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		j++;
	}
	return awards;
	}
	//论文列表显示方法开始
	public List<Paper> getPaperByUser(Paper paper,String date2,Teacher teacher) {
//		String sql= "select * from paper WHERE date>='"+date1+"' and date<='"+date2+"' and papId LIMIT " +
//				""+(pageNow-1)+" ,"+pageSize+"";
		String sql="select * from (select * from paper order by state,type,jclass,meetClass,include,date desc) as acheve,teacher as T where acheve.teaId=T.teaId and T.user=? ";
		int j=0;
		List<Paper> papers=new ArrayList<Paper>();
		List<String> sql1=new ArrayList<String>();
		List<String> paras=new ArrayList<String>();
		
		paras.add(teacher.getUser());
		if(paper!=null)
		{
			if(paper.getDate()!=null)
			{
				sql1.add(" acheve.date>=? and acheve.date<=? ");
				paras.add(String.valueOf(paper.getDate()));
				paras.add(date2);
			}
			if(paper.getName()!=null)
			{
				sql1.add(" acheve.name like ? ");
				paras.add("%"+String.valueOf(paper.getName())+"%");
			}
			if(paper.getEsi()!=null)
			{
				sql1.add(" acheve.esi=? ");
				paras.add(String.valueOf(paper.getEsi()));
			}
			if(paper.getFauthor()!=null)
			{
				sql1.add(" acheve.fauthor=? ");
				paras.add(String.valueOf(paper.getFauthor()));
			}
			if(paper.getInclude()!=null)
			{
				sql1.add(" acheve.include=? ");
				paras.add(String.valueOf(paper.getInclude()));
			}
			if(paper.getJclass()!=null)
			{
				sql1.add(" acheve.jclass=? ");
				paras.add(String.valueOf(paper.getJclass()));
			}
			if(paper.getPapId()!=null)
			{
				sql1.add(" acheve.papId=? ");
				paras.add(String.valueOf(paper.getPapId()));
			}
			if(paper.getPbulication()!=null)
			{
				sql1.add(" acheve.pbulication like ? ");
				paras.add("%"+String.valueOf(paper.getPbulication())+"%");
			}
			if(paper.getPubPage()!=null)
			{
				sql1.add(" acheve.pubPage=? ");
				paras.add(String.valueOf(paper.getPubPage()));
			}
			if(paper.getType()!=null)
			{
				sql1.add(" acheve.type=? ");
				paras.add(String.valueOf(paper.getType()));
			}
			if(paper.getPubNumber()!=null)
			{
				sql1.add(" acheve.pubNumber=? ");
				paras.add(String.valueOf(paper.getPubNumber()));
			}
			if(paper.getTopStu()!=null)
			{
				sql1.add(" acheve.topStu=? ");
				paras.add(String.valueOf(paper.getTopStu()));
			}
			if(paper.getTauthor()!=null)
			{
				sql1.add(" acheve.tauthor like ? ");
				paras.add("%"+String.valueOf(paper.getTauthor())+"%");
			}
			if(paper.getOauthor()!=null)
			{
				sql1.add(" acheve.oauthor like ? ");
				paras.add("%"+String.valueOf(paper.getOauthor())+"%");
			}
			if(paper.getMeetClass()!=null)
			{
				sql1.add(" acheve.meetClass=? ");
				paras.add(String.valueOf(paper.getMeetClass()));
			}
		}
		
		for(int i=0;i<sql1.size();i++)
		{
			sql+="and"+sql1.get(i);
		}
		String[] parameter=new String[paras.size()];
		for(int i=0;i<paras.size();i++)
		{
			parameter[i]=paras.get(i);
		}
		MysqlCrud mysqlcurd=new MysqlCrud();
		List<Map> lists=mysqlcurd.SelectData(sql, parameter);
		//System.out.println(list.size());
		while(j<lists.size()){
			Map map=lists.get(j);
			try {
				Paper paperTwo = new Paper();
				paperTwo.setPapId(Integer.parseInt(map.get(0).toString()));
				paperTwo.setName(map.get(1).toString());
				paperTwo.setFauthor(map.get(2).toString());
				paperTwo.setPbulication(map.get(3).toString());
				paperTwo.setPubNumber(map.get(4).toString());
				paperTwo.setInclude(Integer.parseInt(map.get(5).toString()));
				paperTwo.setQuoteNum(Integer.parseInt(map.get(6).toString()));
				paperTwo.setState(Integer.parseInt(map.get(7).toString()));
				paperTwo.setUpload(map.get(8).toString());
				paperTwo.setPubPage(map.get(9).toString());
				paperTwo.setDate(new SimpleDateFormat("yyyy-MM-dd").parse(map.get(10).toString()));
				paperTwo.setTopStu(Integer.parseInt(map.get(11).toString()));
				paperTwo.setTauthor(map.get(12).toString());
				paperTwo.setOauthor(map.get(13).toString());
				paperTwo.setVolume(map.get(14).toString());
				paperTwo.setTeaId(Integer.parseInt(map.get(15).toString()));
				
				paperTwo.setType(Integer.parseInt(map.get(16).toString()));
				paperTwo.setEsi(Integer.parseInt(map.get(17).toString()));
				paperTwo.setJclass(Integer.parseInt(map.get(18).toString()));
				paperTwo.setIncludNum(map.get(19).toString());
				paperTwo.setMeetClass(Integer.parseInt(map.get(20).toString()));
				paperTwo.setMeetName(map.get(21).toString());
				paperTwo.setMeetPlace(map.get(22).toString());
				papers.add(paperTwo);
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			j++;
		}
		return papers;
	}
	
	public List<Patent> getPatentByUser(Patent patent,String empowerDate2,String applyDate2,Teacher teacher) {
//		String sql= "select * from patent WHERE empowerDate>='"+date1+"' and empowerDate<='"+date2+"' and patId LIMIT " +
//				""+(pageNow-1)+" ,"+pageSize+"";
		String sql="select * from (select * from patent order by examine,expClass,empowerDate desc) as acheve,teacher as T where acheve.teaId=T.teaId and T.user=? ";
		int j=0;
		List<Patent> patents=new ArrayList<Patent>();
		List<String> sql1=new ArrayList<String>();
		List<String> paras=new ArrayList<String>();
		
		paras.add(teacher.getUser());
		if(patent!=null)
		{
			if(patent.getName()!=null)
			{
				sql1.add(" acheve.name like ? ");
				paras.add("%"+String.valueOf(patent.getName())+"%");
			}
			if(patent.getFiestInventor()!=null)
			{
				sql1.add(" acheve.fiestInventor like ? ");
				paras.add("%"+String.valueOf(patent.getFiestInventor())+"%");
			}
			if(patent.getEmpowerDate()!=null)
			{
				sql1.add(" acheve.empowerDate>=? and acheve.empowerDate<=? ");
				paras.add(String.valueOf(patent.getEmpowerDate()));
				paras.add(empowerDate2);
			}
			if(patent.getEmpowerUnit()!=null)
			{
				sql1.add(" acheve.empowerUnit=? ");
				paras.add(String.valueOf(patent.getEmpowerUnit()));
			}
			if(patent.getApplyNumber()!=null)
			{
				sql1.add(" acheve.applyNumber=? ");
				paras.add(String.valueOf(patent.getNumber()));
			}
			if(patent.getExpClass()!=null)
			{
				sql1.add(" acheve.expClass=? ");
				paras.add(String.valueOf(patent.getExpClass()));
			}
			if(patent.getEmpowerNum()!=null)
			{
				sql1.add(" acheve.empowerNum=? ");
				paras.add(String.valueOf(patent.getEmpowerNum()));
			}
			if(patent.getApplyDate()!=null)
			{
				sql1.add(" acheve.applyDate>=? and acheve.applyDate<=? ");
				paras.add(String.valueOf(patent.getApplyDate()));
				paras.add(applyDate2);
			}
		}
		for(int i=0;i<sql1.size();i++)
		{
			sql+="and"+sql1.get(i);
		}
		String[] parameter=new String[paras.size()];
		for(int i=0;i<paras.size();i++)
		{
			parameter[i]=paras.get(i);
		}
		MysqlCrud mysqlcurd=new MysqlCrud();
		List<Map> list = mysqlcurd.SelectData(sql, parameter);
		while(j<list.size()){
			Map map=list.get(j);
			try {
				Patent p = new Patent();
				p.setPatId(Integer.parseInt(map.get(0).toString()));
				p.setName(map.get(1).toString());
				p.setFiestInventor(map.get(2).toString());
				p.setEmpowerDate(new SimpleDateFormat("yyyy-MM-dd").parse(map.get(3).toString()));
				p.setEmpowerUnit(map.get(4).toString());
				p.setTransferFunds(Integer.parseInt(map.get(5).toString()));
				p.setExamine(Integer.parseInt(map.get(6).toString()));
				p.setApplyNumber(map.get(7).toString());
				p.setNumber(map.get(8).toString());
				p.setUpload(map.get(9).toString());
				p.setTeaId(Integer.parseInt(map.get(10).toString()));
				
				p.setExpClass(Integer.parseInt(map.get(11).toString()));
				p.setEmpowerNum(map.get(12).toString());
				p.setApplyDate(StringDate.StringToDate(map.get(13).toString()));
				patents.add(p);
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			j++;
		}
		return patents;
	}
	
	public List<Project> getProjectByUser(Project project,Teacher teacher) {
//		String sql= "select * from project WHERE finishDate>='"+date1+"' and finishDate<='"+date2+"' and proId LIMIT " +
//				""+(pageNow-1)+" ,"+pageSize+"";
		String sql="select * from (select * from project order by state,level,kind,class,finishDate desc) as acheve,teacher as T where acheve.teaId=T.teaId and T.user=? ";
		int j=0;
		List<String> sql1=new ArrayList<String>();
		List<String> paras=new ArrayList<String>();
		List<Project> projects=new ArrayList<Project>();
		
		paras.add(teacher.getUser());
		if(project!=null)
		{
			if(project.getName()!=null)
			{
				sql1.add(" acheve.name like ? ");
				paras.add("%"+String.valueOf(project.getName())+"%");
			}
			if(project.getLevel()!=null)
			{
				sql1.add(" acheve.level=? ");
				paras.add(String.valueOf(project.getLevel()));
			}
			if(project.getFinishDate()!=null)
			{
				sql1.add(" acheve.finishDate>=? and acheve.finishDate<=? ");
				paras.add(String.valueOf(project.getBeginDate()));
				paras.add(String.valueOf(project.getFinishDate()));
			}
			if(project.getSource()!=null)
			{
				sql1.add(" acheve.source=? ");
				paras.add(String.valueOf(project.getSource()));
			}
			if(project.getFunds()!=null)
			{
				sql1.add(" acheve.funds=? ");
				paras.add(String.valueOf(project.getFunds()));
			}
			if(project.getArriveFunds()!=null)
			{
				sql1.add(" acheve.arriveFunds=? ");
				paras.add(String.valueOf(project.getArriveFunds()));
			}
			if(project.getLeader()!=null)
			{
				sql1.add(" acheve.leader like ? ");
				paras.add("%"+String.valueOf(project.getLeader())+"%");
			}
			if(project.getOthers()!=null)
			{
				sql1.add(" acheve.others like ? ");
				paras.add("%"+String.valueOf(project.getOthers())+"%");
			}
			if(project.getClass_()!=null)
			{
				sql1.add(" acheve.class=? ");
				paras.add(String.valueOf(project.getClass_()));
			}
			if(project.getNumber()!=null)
			{
				sql1.add(" acheve.number=? ");
				paras.add(String.valueOf(project.getNumber()));
			}
			if(project.getKind()!=null)
			{
				sql1.add(" acheve.kind=? ");
				paras.add(String.valueOf(project.getKind()));
			}
			if(project. getProgress()!=null)
			{
				sql1.add(" acheve.progress=? ");
				paras.add(String.valueOf(project. getProgress()));
			}
		}
		for(int i=0;i<sql1.size();i++)
		{
			sql+="and"+sql1.get(i);
		}
		String[] parameter=new String[paras.size()];
		for(int i=0;i<paras.size();i++)
		{
			parameter[i]=paras.get(i);
		}
		MysqlCrud mysqlcurd=new MysqlCrud();
		List<Map> list = mysqlcurd.SelectData(sql, parameter);
		//System.out.println(list.size());
		while(j<list.size()){
			Map map=list.get(j);
			try {
				Project p = new Project();
				p.setProId(Integer.parseInt(map.get(0).toString()));
				p.setName(map.get(1).toString());
				p.setLevel(Integer.parseInt(map.get(2).toString()));
				p.setSource(map.get(3).toString());
				p.setFunds(Integer.parseInt(map.get(4).toString()));
				p.setArriveFunds(Integer.parseInt(map.get(5).toString()));
				p.setLeader(map.get(6).toString());
				p.setOthers(map.get(7).toString());
				p.setBeginDate(new SimpleDateFormat("yyyy-MM-dd").parse(map.get(8).toString()));
				p.setState(Integer.parseInt(map.get(9).toString()));
				p.setLowDepartment(map.get(10).toString());
				p.setClass_(Integer.parseInt(map.get(11).toString()));
				p.setNumber(map.get(12).toString());
				p.setFinishDate(new SimpleDateFormat("yyyy-MM-dd").parse(map.get(13).toString()));
				p.setUpload(map.get(14).toString());
				p.setTeaId(Integer.parseInt(map.get(15).toString()));
				p.setKind(Integer.parseInt(map.get(16).toString()));
				p.setProgress(Integer.parseInt(map.get(17).toString()));
				projects.add(p);
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			j++;
		}
		return projects;
	}
	
	public List<Treatise> getTreatiseByUser(Treatise treatise,String date2,Teacher teacher) {
//		String sql= "select * from treatise WHERE date>='"+date1+"' and date<='"+date2+"' and treId LIMIT " +
//				""+(pageNow-1)+" ,"+pageSize+"";
		int j=0;
		String sql="select * from (select * from treatise order by state,class,date desc) as acheve,teacher as T where acheve.teaId=T.teaId and T.user=? ";
		List<Treatise> treatises=new ArrayList<Treatise>();
		List<String> sql1=new ArrayList<String>();
		List<String> paras=new ArrayList<String>();
		
		paras.add(teacher.getUser());
		if(treatise!=null)
		{
			if(treatise.getName()!=null)
			{
				sql1.add(" acheve.name like ? ");
				paras.add("%"+String.valueOf(treatise.getName())+"%");
			}
			
			if(treatise.getDate()!=null)
			{
				sql1.add(" acheve.date>=? and acheve.date<=? ");
				paras.add(String.valueOf(treatise.getDate()));
				paras.add(date2);
			}
			if(treatise.getAuthor()!=null)
			{
				sql1.add(" acheve.author like ? ");
				paras.add("%"+String.valueOf(treatise.getAuthor())+"%");
			}
			if(treatise.getState()!=null)
			{
				sql1.add(" acheve.state=? ");
				paras.add(String.valueOf(treatise.getState()));
			}
			if(treatise.getClass_()!=null)
			{
				sql1.add(" acheve.class=? ");
				paras.add(String.valueOf(treatise.getClass_()));
			}
			if(treatise.getPress()!=null)
			{
				sql1.add(" acheve.press=? ");
				paras.add(String.valueOf(treatise.getPress()));
			}
			if(treatise.getBookNum()!=null)
			{
				sql1.add(" acheve.bookNum=? ");
				paras.add(String.valueOf(treatise.getBookNum()));
			}
		}
		for(int i=0;i<sql1.size();i++)
		{
			sql+="and"+sql1.get(i);
		}
		String[] parameter=new String[paras.size()];
		for(int i=0;i<paras.size();i++)
		{
			parameter[i]=paras.get(i);
		}
		MysqlCrud mysqlcurd=new MysqlCrud();
		List<Map> list = mysqlcurd.SelectData(sql, parameter);
		//System.out.println(list.size());
		while(j<list.size()){
			Map map=list.get(j);
			try {
				Treatise t = new Treatise();
				t.setTreId(Integer.parseInt(map.get(0).toString()));
				t.setName(map.get(1).toString());
				t.setAuthor(map.get(2).toString());
				t.setDate(new SimpleDateFormat("yyyy-MM-dd").parse(map.get(3).toString()));
				t.setState(Integer.parseInt(map.get(4).toString()));
				t.setClass_(Integer.parseInt(map.get(5).toString()));
				t.setPress(map.get(6).toString());
				t.setAbstract_(map.get(7).toString());
				t.setUpload(map.get(8).toString());
				t.setBookNum(map.get(9).toString());
				t.setTeaId(Integer.parseInt(map.get(10).toString()));
				treatises.add(t);
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			j++;
		}
		return treatises;
	}
}